package top.chengdongqing.mishop.repositories

import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query
import top.chengdongqing.mishop.entities.Order
import top.chengdongqing.mishop.enums.OrderStatus
import java.time.LocalDateTime

interface OrderRepository : JpaRepository<Order, Int> {

    fun findByIdAndUserId(id: Int, userId: Int): Order?

    @Query(
        """
        FROM Order o
        LEFT JOIN OrderItem oi ON o.id = oi.order.id
        WHERE
            o.userId = :userId
            AND (:status IS NULL OR o.status = :status)
            AND (:keyword IS NULL OR o.orderNumber LIKE %:keyword% OR oi.productName LIKE %:keyword%)
        GROUP BY o.id
        ORDER BY o.createdAt DESC
    """
    )
    fun findAllByPage(userId: Int, keyword: String?, status: OrderStatus?, pageable: Pageable): Page<Order>

    fun findAllByStatusAndCreatedAtLessThanEqual(status: OrderStatus, cutoff: LocalDateTime): List<Order>

    fun findAllByUserIdAndStatusAndIsReviewedFalse(userId: Int, status: OrderStatus, pageable: Pageable): List<Order>

    @Query(
        """
        SELECT
            o.status AS key,
            COUNT(*) AS value
        FROM
            Order o
        WHERE
            o.userId = :userId
            AND o.status IN :statuses
            AND o.isReviewed = false
        GROUP BY
            o.status
    """
    )
    fun countByUserIdAndStatusIn(userId: Int, statuses: List<OrderStatus>): List<Array<Any>>

    fun existsByUserIdAndStatusNotIn(userId: Int, statuses: List<OrderStatus>): Boolean
}